package com.proactive.webapp.dao.impl;

import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.jdbc.Statement;
import com.proactive.webapp.dao.CourseDAO;
import com.proactive.webapp.entity.Course;
import com.proactive.webapp.entity.Member;
import com.proactive.webapp.util.DateConversion;

public class CourseDAOImpl implements CourseDAO{
	DateConversion dateConversion = new DateConversion();
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public int insert(Course course) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO course(courseCategory1ID, courseCategory2ID, courseTitle, courseImage, "
				+ "courseContent, courseDemo, courseFile, courseFileUrl, isFile, courseWriter, courseWriterUrl, "
				+ "isWriter, memberAccount, courseStatus, courseFileName, createDate, updateDate) VALUES"
				+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())";
		int courseID = 0; 
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			smt.setInt(1, course.getCategory1ID());
			smt.setInt(2, course.getCategory2ID());
			smt.setString(3, course.getTitle());
			smt.setString(4, course.getImagePath());
			smt.setString(5, course.getContent());
			smt.setString(6, course.getDemo());
			smt.setString(7, course.getFilePath());
			smt.setString(8, course.getFileUrl());
			smt.setInt(9, course.getIsFile());
			smt.setString(10, course.getWriter());
			smt.setString(11, course.getWriterUrl());
			smt.setInt(12, course.getIsWriter());
			smt.setString(13, course.getAccount());
			smt.setInt(14, course.getStatus());
			smt.setString(15, course.getFileName());
			smt.executeUpdate();
			rs = smt.getGeneratedKeys();
			if(rs.next()){
				courseID = rs.getInt(1);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return courseID;
	}

	public void update(Course course) {
		// TODO Auto-generated method stub
		String sql = "UPDATE course SET courseCategory1ID=?, courseCategory2ID=?, courseTitle=?, "
				+ "courseImage=?, courseContent=?, courseDemo=?, courseFileName=?, courseFile=?, "
				+ "courseFileUrl=?, isFile=?, courseWriter=?, courseWriterUrl=?, isWriter=?, "
				+ "updateDate=NOW() WHERE courseID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, course.getCategory1ID());
			smt.setInt(2, course.getCategory2ID());
			smt.setString(3, course.getTitle());
			smt.setString(4, course.getImagePath());
			smt.setString(5, course.getContent());
			smt.setString(6, course.getDemo());
			smt.setString(7, course.getFileName());
			smt.setString(8, course.getFilePath());
			smt.setString(9, course.getFileUrl());
			smt.setInt(10, course.getIsFile());
			smt.setString(11, course.getWriter());
			smt.setString(12, course.getWriterUrl());
			smt.setInt(13, course.getIsWriter());
			smt.setInt(14, course.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void updateStatus(Course course) {
		// TODO Auto-generated method stub
		String sql = "UPDATE course SET courseStatus = ? WHERE courseID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, course.getStatus());
			smt.setInt(2, course.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public void updateViewCount(Course course) {
		// TODO Auto-generated method stub
		String sql = "UPDATE course SET courseViewCount = ? WHERE courseID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, course.getViewCount());
			smt.setInt(2, course.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public Course get(Course course) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
				+ "AS reportCount FROM course a JOIN course_category1 e ON a.courseCategory1ID = e.courseCategory1ID "
				+ "JOIN course_category2 f ON a.courseCategory2ID = f.courseCategory2ID "
				+ "JOIN member g ON a.memberAccount = g.memberAccount "
				+ "WHERE courseID = ? HAVING reportCount < 10";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, course.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setMemberNO(rs.getString("memberNO"));
				course.setMember(member);
				course.setCategory1Name(rs.getString("courseCategory1Name"));
				course.setCategory2Name(rs.getString("courseCategory2Name"));
				course.setCategory1ID(rs.getInt("courseCategory1ID"));
				course.setCategory2ID(rs.getInt("courseCategory2ID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setContent(rs.getString("courseContent"));
				course.setImagePath(rs.getString("courseImage"));
				course.setDemo(rs.getString("courseDemo"));		
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsWriter(rs.getInt("isWriter"));
				course.setWriter(rs.getString("courseWriter"));
				course.setWriterUrl(rs.getString("courseWriterUrl"));
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				course.setAccount(rs.getString("memberAccount"));
				course.setStatus(rs.getInt("courseStatus"));
				course.setCreateDate(rs.getDate("createDate"));
				course.setCreateDateString(dateConversion.ConversionYDM(rs.getDate("createDate")));
				course.setCategoryUrl("course?c1="+course.getCategory1ID()+"&c2="+course.getCategory2ID());
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return course;
	}
	
	public Course getByWriter(Course course) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
				+ "AS reportCount FROM course a JOIN course_category1 e ON a.courseCategory1ID = e.courseCategory1ID "
				+ "JOIN course_category2 f ON a.courseCategory2ID = f.courseCategory2ID "
				+ "JOIN member g ON a.memberAccount = g.memberAccount "
				+ "WHERE courseID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, course.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setMemberNO(rs.getString("memberNO"));
				course.setMember(member);
				course.setCategory1Name(rs.getString("courseCategory1Name"));
				course.setCategory2Name(rs.getString("courseCategory2Name"));
				course.setCategory1ID(rs.getInt("courseCategory1ID"));
				course.setCategory2ID(rs.getInt("courseCategory2ID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setContent(rs.getString("courseContent"));
				course.setImagePath(rs.getString("courseImage"));
				course.setDemo(rs.getString("courseDemo"));		
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsWriter(rs.getInt("isWriter"));
				course.setWriter(rs.getString("courseWriter"));
				course.setWriterUrl(rs.getString("courseWriterUrl"));
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				course.setAccount(rs.getString("memberAccount"));
				course.setStatus(rs.getInt("courseStatus"));
				course.setCreateDate(rs.getDate("createDate"));
				course.setCreateDateString(dateConversion.ConversionYDM(rs.getDate("createDate")));
				course.setCategoryUrl("course?c1="+course.getCategory1ID()+"&c2="+course.getCategory2ID());
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return course;
	}

	public List<Course> getListByType(String type, int start, int size) {
		// TODO Auto-generated method stub
		List<Course> courseList = new ArrayList<Course>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY courseViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "com":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY commentCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "fav":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY favoriteCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, start);
			smt.setInt(2, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Course course = new Course();
				course.setId(rs.getInt("courseID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setImagePath(rs.getString("courseImage"));
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));	
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsFile(rs.getInt("isFile"));
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				courseList.add(course);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return courseList;
	}

	public List<Course> getListByCategory1(String type, int courseCategory1ID, int start, int size) {
		// TODO Auto-generated method stub
		List<Course> courseList = new ArrayList<Course>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory1ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory1ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY courseViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "com":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory1ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY commentCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "fav":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory1ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY favoriteCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory1ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, courseCategory1ID);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Course course = new Course();
				course.setId(rs.getInt("courseID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setImagePath(rs.getString("courseImage"));
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));	
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsFile(rs.getInt("isFile"));
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				courseList.add(course);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return courseList;
	}

	public List<Course> getListByCategory2(String type, int courseCategory2ID, int start, int size) {
		// TODO Auto-generated method stub
		List<Course> courseList = new ArrayList<Course>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory2ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory2ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY courseViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "com":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory2ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY commentCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "fav":
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory2ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY favoriteCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
						+ "AS reportCount FROM course a WHERE courseCategory2ID = ? AND courseStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, courseCategory2ID);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Course course = new Course();
				course.setId(rs.getInt("courseID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setImagePath(rs.getString("courseImage"));
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));	
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsFile(rs.getInt("isFile"));
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				courseList.add(course);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return courseList;
	}

	@Override
	public int countTotal(int courseCategory1ID, int courseCategory2ID) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			if(courseCategory1ID != 0 && courseCategory2ID == 0){
				String sql = "SELECT *,(SELECT COUNT(*) FROM course_report b WHERE a.courseID = b.courseID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM course a WHERE courseCategory1ID = ? AND courseStatus = 1 "
						+ "HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
				smt.setInt(1, courseCategory1ID);
			}
			else if(courseCategory1ID == 0 && courseCategory2ID != 0){
				String sql = "SELECT *,(SELECT COUNT(*) FROM course_report b WHERE a.courseID = b.courseID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM course a WHERE courseCategory2ID = ? AND courseStatus = 1 "
						+ "HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
				smt.setInt(1, courseCategory2ID);				
			}
			else{
				String sql = "SELECT *,(SELECT COUNT(*) FROM course_report b WHERE a.courseID = b.courseID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM course a WHERE courseStatus = 1 HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
			}
			
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public Course getByLogin(Course course, String loginAccount) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
				+ "AS reportCount,(SELECT COUNT(*) FROM course_favorite e WHERE a.courseID = e.courseID AND e.memberAccount=?) "
				+ "AS favorite,(SELECT COUNT(*) FROM course_collect f WHERE a.courseID = f.courseID AND f.memberAccount=?) "
				+ "AS collect,(SELECT COUNT(*) FROM course_report g WHERE a.courseID = g.courseID AND g.memberAccount=?) "
				+ "AS report FROM course a JOIN course_category1 h ON a.courseCategory1ID = h.courseCategory1ID "
				+ "JOIN course_category2 i ON a.courseCategory2ID = i.courseCategory2ID "
				+ "JOIN member j ON a.memberAccount = j.memberAccount "
				+ "WHERE courseID = ? HAVING reportCount < 10";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setString(3, loginAccount);
			smt.setInt(4, course.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setMemberNO(rs.getString("memberNO"));
				course.setMember(member);
				course.setCategory1Name(rs.getString("courseCategory1Name"));
				course.setCategory2Name(rs.getString("courseCategory2Name"));
				course.setCategory1ID(rs.getInt("courseCategory1ID"));
				course.setCategory2ID(rs.getInt("courseCategory2ID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setContent(rs.getString("courseContent"));
				course.setImagePath(rs.getString("courseImage"));
				course.setDemo(rs.getString("courseDemo"));		
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));		
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsWriter(rs.getInt("isWriter"));
				course.setWriter(rs.getString("courseWriter"));
				course.setWriterUrl(rs.getString("courseWriterUrl"));
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				course.setAccount(rs.getString("memberAccount"));
				course.setStatus(rs.getInt("courseStatus"));
				course.setCreateDate(rs.getDate("createDate"));
				course.setCreateDateString(dateConversion.ConversionYDM(rs.getDate("createDate")));
				course.setCategoryUrl("course?c1="+course.getCategory1ID()+"&c2="+course.getCategory2ID());
				course.setCollectFlag(rs.getInt("collect")==0?false:true);
				course.setFavoriteFlag(rs.getInt("favorite")==0?false:true);
				course.setReportFlag(rs.getInt("report")==0?false:true);
				course.setWriterFlag(member.getAccount().equals(loginAccount)?true:false);
				course.setFavoriteString1(course.isFavoriteFlag()?"none":"inline");
				course.setFavoriteString2(course.isFavoriteFlag()?"inline":"none");
				course.setFavoriteClass(course.isFavoriteFlag()?"topfocusLove":"");
				course.setCollectString(course.isCollectFlag()?"已收藏":"收藏");
				course.setCollectString1(course.isCollectFlag()?"none":"inline");
				course.setCollectString2(course.isCollectFlag()?"inline":"none");
				course.setCollectClass(course.isCollectFlag()?"topfocusCollect":"");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return course;
	}

	@Override
	public List<Course> getListByCategory2Top3(int category2ID, int currentCourseID) {
		// TODO Auto-generated method stub
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
				+ "AS reportCount FROM course a JOIN course_category2 c ON a.courseCategory2ID = c.courseCategory2ID "
				+ "WHERE a.courseCategory2ID = ? AND courseID != ? AND courseStatus = 1 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT 3";
		
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, category2ID);
			smt.setInt(2, currentCourseID);
			rs = smt.executeQuery();
			while(rs.next()){
				Course course = new Course();
				course.setId(rs.getInt("courseID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setImagePath(rs.getString("courseImage"));
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));	
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsFile(rs.getInt("isFile"));
				courseList.add(course);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return courseList;
	}

	@Override
	public List<Course> getListByMember(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
				+ "AS reportCount FROM course a WHERE memberAccount = ? AND courseStatus = 1 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Course course = new Course();
				course.setId(rs.getInt("courseID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setImagePath(rs.getString("courseImage"));
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));	
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsFile(rs.getInt("isFile"));
				course.setStatus(rs.getInt("courseStatus"));
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				courseList.add(course);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return courseList;
	}

	@Override
	public List<Course> getListByWriter(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_comment b WHERE topCommentID IS NULL AND a.courseID = b.courseID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM course_favorite c WHERE a.courseID = c.courseID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM course_report d WHERE a.courseID = d.courseID) "
				+ "AS reportCount FROM course a WHERE memberAccount = ? AND courseStatus != 3 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Course course = new Course();
				course.setId(rs.getInt("courseID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setImagePath(rs.getString("courseImage"));
				course.setIsFile(rs.getInt("isFile"));
				course.setFileName(rs.getString("courseFileName"));	
				course.setFilePath(rs.getString("courseFile"));
				course.setDownloadUrl(course.getFilePath().equals("")?"":("download?t=1&id="+course.getId()));
				course.setFileUrl(rs.getString("courseFileUrl"));
				course.setIsFile(rs.getInt("isFile"));
				course.setStatus(rs.getInt("courseStatus"));
				course.setActString(course.getStatus()==2?"顯示":"隱藏");
				course.setActClass(course.getStatus()==2?"show":"hide");
				course.setHideClass(course.getStatus()==2?"edithide":"");
				course.setViewCount(rs.getInt("courseViewCount"));
				course.setFavoriteCount(rs.getInt("favoriteCount"));
				course.setCommentCount(rs.getInt("commentCount"));
				courseList.add(course);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return courseList;
	}

	@Override
	public int countTotalByMember(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM course_report b WHERE a.courseID = b.courseID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM course a WHERE memberAccount = ? AND courseStatus = 1 "
					+ "HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public int countTotalByWriter(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM course_report b WHERE a.courseID = b.courseID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM course a WHERE memberAccount = ? AND courseStatus != 3 HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Course> getListByMemberTop(String account, int number) {
		// TODO Auto-generated method stub
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_report b WHERE a.courseID = b.courseID) "
				+ "AS reportCount FROM course a WHERE memberAccount = ? AND courseStatus = 1 "
				+ "HAVING reportCount < 10 ORDER BY createDate DESC LIMIT ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, number);
			rs = smt.executeQuery();
			while(rs.next()){
				Course course = new Course();
				course.setId(rs.getInt("courseID"));
				course.setTitle(rs.getString("courseTitle"));
				course.setImagePath(rs.getString("courseImage"));
				courseList.add(course);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return courseList;
	}

	@Override
	public List<Course> getListTop(int number) {
		// TODO Auto-generated method stub
		return null;
	}


}
